{% extends 'base.html' %}
{% load staticfiles %}

{% block head %}
    <link rel="stylesheet"
          href="{% static 'AdminLTE/bower_components/datatables.net-bs/css/dataTables.bootstrap.min.css' %}">
    <!-- Select2 -->
    <link rel="stylesheet" href="{% static 'AdminLTE/bower_components/select2/dist/css/select2.min.css' %}">
    <link href="{% static 'bootstrap-fileinput/css/fileinput.min.css' %}" media="all" rel="stylesheet" type="text/css"/>
    <link rel="stylesheet" href="{% static 'jquery-confirm/dist/jquery-confirm.min.css' %}">

    <style>
        .has-input-error {
            border: 1px solid #dd4b39;
        }

        #edit-content {
            width: 100%;
            height: 400px;
        }

        .sql-one, .sql-export, .sql-many, .sql-select {
            width: 10%;
            float: left;
            display: inline;
            margin-left: 2px;
            margin-right: 0;
        }

        .btn-block + .btn-block {
            margin-top: 0;
        }

        table {
            border-collapse: collapse;
        }

        /* dataTables列内容居中 */
        #sql-res-table > tbody > tr > td {
            text-align: center;
        }

        /* dataTables表头居中 */
        #sql-res-table > thead:first-child > tr:first-child > th {
            text-align: center;
        }
    </style>
{% endblock %}

{% block content %}

    <div class="row">
        <div class="col-md-3">
            <div class="box">
                <div class="box-body seach-db">
                    <div class="form-group">
                        <label for="db_type">选择数据库</label>
                        <select class="form-control" name="db_type" id="db_type" style="width: 100%;" onchange="clearDBServer();">
                            <option value="0">Mysql</option>
                            <option value="1">Oracle</option>
                        </select>
                    </div>

                    <div class="form-group">
                        <label for="db_server">选择数据库</label>
                        <select class="form-control select2" name="db_server" id="db_server" style="width: 100%;">
                        </select>
                    </div>
                    <button type="button" class="btn btn-success btn-sm connect" style="width: 100%">连接</button>

                </div>

                <div class="table-name" style="display: none;">
                    <div class="box-header with-border">
                        <h3 class="box-title text-center" style="display: block">
                            表名称
                        </h3>
                    </div>
                    <div class="box-body table-names" style="overflow: auto;text-align: center;height: 800px">
                        <table rules="all" frame="box" style="text-align: center; width: auto;display:inline-block;">
                            <tbody class="names" data-status="0">

                            </tbody>
                        </table>
                    </div>
                </div>
            </div>
        </div>

        <div class="col-md-9">
            <div class="box">
                <div class="box-header with-border">
                    <h3 class="box-title" style="padding-top: 8px;">SQL内容</h3>
                    <div class="col-sm-6" style="float: right">
                        <input type="file" id="upload_file" class="file" name="upload_file" required>
                    </div>
                </div>
                <div class="box-body no-padding">
                    <div class="row" style="margin-right: 0; margin-left: 0;">
                        <div id="edit-content"></div>
                    </div>
                </div>
            </div>

            <div class="row" style="margin-right: 0; margin-left: 0;">
                <button type="button" class="btn btn-block btn-warning btn-sm sql-one" data-toggle="tooltip"
                        title="用于执行update,insert,delete等操作语句">执行
                </button>
                <button type="button" class="btn btn-block btn-warning btn-sm sql-many" data-toggle="tooltip"
                        title="用于同时执行多条语句，一般用于insert或replace语句，格式必须是：insert test values (%s, %s) args=[('name1', 18), ('name2', 18)]">
                    批量执行
                </button>
                <button type="button" class="btn btn-block btn-primary btn-sm sql-select" data-toggle="tooltip"
                        title="用于执行select或show等查询语句">查询
                </button>
            </div>

            <div class="row" style="margin-right: 0; margin-left: 0; margin-top: 5px; height:700px;overflow: auto;">
                <div class="box box-solid">
                    <div class="box-body">
                      <div class="box-group" id="sql-res">
                        <div class="panel box box-primary">
                          <div class="box-header with-border">
                            <h4 class="box-title">
                              <a data-toggle="collapse" data-parent="#sql-res" href="#collapseOne" aria-expanded="true">
                                执行结果
                              </a>
                            </h4>
                          </div>
                          <div id="collapseOne" class="panel-collapse collapse in" aria-expanded="true">
                            <div class="box-body">
                                <div class="tab-pane active" id="sql-res-output"></div>
                            </div>
                          </div>
                        </div>
                      </div>
                    </div>
                </div>
            </div>

        </div>
    </div>
    <!-- /.row -->

{% endblock %}


{% block js %}
    <!-- DataTables -->
    <script src="{% static 'AdminLTE/bower_components/datatables.net/js/jquery.dataTables.min.js' %}"></script>
    <script src="{% static 'AdminLTE/bower_components/datatables.net-bs/js/dataTables.bootstrap.min.js' %}"></script>
    <!-- Select2 -->
    <script src="{% static 'AdminLTE/bower_components/select2/dist/js/select2.full.min.js' %}"></script>
    <script src="{% static 'bootstrap-fileinput/js/fileinput.min.js' %}"></script>
    <script src="{% static 'ace/js/ace.js' %}"></script>
    <script src="{% static 'ace/js/mode-mysql.js' %}"></script>
    <script src="{% static 'ace/js/theme-monokai.js' %}"></script>
    <script src="{% static 'ace/js/ext-language_tools.js' %}"></script>
    <script src="{% static 'jquery-confirm/dist/jquery-confirm.min.js' %}"></script>

    <script>
        $(function () {
            $("[data-toggle='tooltip']").tooltip();
            $('.select2').select2({
                allowClear: true,
                ajax: {
                    url: '/db_config/find_dbconfig/',
                    dataType: 'json',
                    data: function (params) {
                        params.db_type = $('#db_type').val()
                        params.offset = 6
                        params.page = params.page || 1
                        var query = {
                            search: params.term,
                            page: params.page,
                            offset: params.offset,
                            db_type: params.db_type
                        }
                        return query;
                    },
                    processResults: function (data, params) {
                        var page = params.page || 1
                        return {
                            results: data.results,
                            pagination: {
                                more: (page*params.offset) < data.total_count
                            }
                        };
                    },
                    cache: true
                  },
                minimumInputLength: 2,
                templateResult: formatRepo,
                templateSelection: formatRepoSelection,
                placeholder: '查询IP地址 项目编号/名称 库名/实例名 账号'
            });

            ace.require("ace/ext/language_tools");
            editor = ace.edit("edit-content");
            editor.setTheme("ace/theme/monokai");
            editor.session.setMode("ace/mode/mysql");
            editor.setShowPrintMargin(false);
            editor.getSession().setUseWrapMode(true);
            editor.setOptions({
                enableBasicAutocompletion: true,
                enableSnippets: true,
                enableLiveAutocompletion: true
            });
        });

        function clearOutput(){
            $('#sql-res-output').empty()
            $('.table-collapse').remove()
            $('#collapseOne:not(.in)').attr({'aria-expanded': true, 'style': ''}).toggleClass('in').siblings().children().children().attr('aria-expanded',true).removeClass('collapsed')
        }

        function clearDBServer(){
            $("#db_server").val('').trigger('change');
        }

        function cleanError(){
            $('.has-input-error').removeClass('has-input-error')
            $('.has-error').removeClass('has-error')
            $('.help-block').remove()
        }

        function formatRepo (repo) {
            if (repo.loading) {
                return repo.text;
            }
            var $container = $("<div class='select2-result-repository clearfix'>" +
                  "<div class='select2-result-repository-element'>" + repo.text +
                "</div></div>");
            return $container;
        }

        function formatRepoSelection (repo) {
            return repo.text;
        }

        $("#upload_file").fileinput({
            language: 'zh',
            showUpload: true,
            dropZoneEnabled: false,
            msgPlaceholder: '只能上传后缀是.txt或.sql的文件',
            allowedFileExtensions: ["txt", "sql"],
            showPreview: false,
            showCaption: true,
            maxFileSize: 10240,
            uploadUrl: "{% url 'db_exec' %}",
            mergeAjaxCallbacks: true,
            ajaxSettings: {
                headers: {'X-CSRFToken': '{{ csrf_token }}'},
                success: function (response) {
                    if(response.code==200){
                        editor.setValue(response.data, -1)
                    } else {
                        $.alert({
                            title: 'Tips',
                            type: 'red',
                            content: response.msg,
                        })
                    }
                },
                error: function (response) {
                    $.alert({
                        title: 'Tips',
                        type: 'red',
                        content: '上传失败！',
                    })
                },
            },
            browseLabel: '浏览文件',
            removeLabel: '删除',
            uploadLabel: '上传',
        });

        // 连接数据库获取所有的表
        $('.connect').on('click', function () {
            cleanError()
            if(!$('#db_server').val()){
                $('#db_server').parent().append('<span class="help-block">该选项不能为空。</span>')
                $('#db_server').next().addClass('has-input-error').parent().parent().addClass('has-error')
                return
            }

            let data = {
                pk: $('#db_server').val(),
                sql: "show tables"
            };
            $('.names').attr('data-status', 0)
            $.ajax({
                url: "{% url 'db_exec' %}",
                type: 'POST',
                data: data,
                success: function (res) {
                    if (res.code === 200) {
                        $('.table-name').css('display', 'block');
                        let names = res.data;
                        let table = $('.names');
                        table.html('');
                        for (let i = 0; i < names.length; i++) {
                            table.append(`<tr> <td>${names[i]}</td> </tr>`)
                        }
                        $('.names').attr('data-status', 1)
                    } else {
                        $.alert({
                            title: 'Tips',
                            type: 'red',
                            content: res.msg,
                        })
                    }
                },
                error: function (response) {
                    $.alert({
                        title: 'Tips',
                        type: 'red',
                        content: response.responseText,
                    })
                }
            })
        });

        // 连接数据库执行查询语句
        $('.sql-select').on('click', function () {
            let sql = editor.getSession().getValue();
            let names = $('.names').text();
            let status = $('.names').attr('data-status')
            if (status === '0') {
                $.alert({
                    title: 'Tips',
                    type: 'red',
                    content: '请正确连接数据库',
                })
            } else if (sql.length === 0 && $('#upload_file').val().length === 0) {
                $.alert({
                    title: 'Tips',
                    type: 'red',
                    content: '请正确输入SQL语句或上传SQL文件',
                })
            } else {
                $.ajax({
                    url: "{% url 'db_exec' %}",
                    type: 'POST',
                    data: {pk: $('#db_server').val(), sql: sql, sql_type: 'select'},
                    success: function (res) {
                        clearOutput()
                        if (res.code === 200) {
                            var data = res.data
                            var out = ''
                            for(i in data){
                                if(data[i].hasOwnProperty('table_heads')){
                                    var html = '<div class="panel box box-primary table-collapse">\n' +
                                        '<div class="box-header with-border">\n' +
                                        '<h4 class="box-title" style="width: 100%;">\n' +
                                        '<a data-toggle="collapse" data-parent="#accordion" href="#collapse'+i+'" class="collapsed" aria-expanded="false">\n' +
                                        'SQL语句' + (Number(i)+1) +
                                        '\n</a><span style="float:right;"><a type="button" class="btn btn-success btn-xs sql-export" style="width:100%;" onclick="sqlExport('+data[i].db_log_id+');"><i class="fa fa-download"></i>导出结果</a></span></h4></div>\n' +
                                        '<div id="collapse'+i+'" class="panel-collapse collapse" aria-expanded="false" style="height: 0px;">\n' +
                                        '<div class="box-body">\n' +
                                        '<table id="sql-res-table'+i+'" class="table table-bordered table-hover cell-border"></table>\n' +
                                        '</div></div></div>'
                                    $('#sql-res').append(html)
                                    var sql_res = $('#sql-res-table'+i);
                                    let columnList = [];
                                    for (let j = 0; j < data[i].table_heads.length; j++) {
                                        columnList.push({'title': data[i].table_heads[j]})
                                    }
                                    var sql_table = sql_res.DataTable({
                                        'paging': true,
                                        'ordering': true,
                                        'info': true,
                                        'autoWidth': false,
                                        'columns': columnList,
                                        'retrieve': true
                                    });
                                    sql_table.rows.add(data[i].data).draw();
                                    out += '<li>' + data[i].msg + '</li>'
                                } else {
                                    out += '<li>' + data[i].data + '</li>'
                                }
                                $('#sql-res-output').html('<ul class="list-unstyled">'+out+'</ul>');
                            }
                        } else {
                            $.alert({
                                title: 'Tips',
                                type: 'red',
                                content: res.msg,
                            })
                        }
                    },
                    error: function (response) {
                        $.alert({
                            title: 'Tips',
                            type: 'red',
                            content: response.responseText,
                        })
                    }
                })
            }
        });

        // 连接数据库执行单条语句
        $('.sql-one').on('click', function () {
            let sql = editor.getSession().getValue();
            let names = $('.names').text();
            let status = $('.names').attr('data-status')
            if (status === '0') {
                $.alert({
                    title: 'Tips',
                    type: 'red',
                    content: '请正确连接数据库',
                })
            } else if (sql.length === 0) {
                $.alert({
                    title: 'Tips',
                    type: 'red',
                    content: '请正确输入SQL语句',
                })
            } else {
                $.ajax({
                    url: "{% url 'db_exec' %}",
                    type: 'POST',
                    data: {pk: $('#db_server').val(), sql: sql, sql_type: 'sql-one'},
                    success: function (res) {
                        clearOutput()
                        if (res.code === 200) {
                            var out = ''
                            for(l of res.data){
                                out += '<li>' + l + '</li>'
                            }
                            $('#sql-res-output').html('<ul class="list-unstyled">'+out+'</ul>');
                        } else {
                            $.alert({
                                title: 'Tips',
                                type: 'red',
                                content: res.msg,
                            })
                        }
                    },
                    error: function (response) {
                        $.alert({
                            title: 'Tips',
                            type: 'red',
                            content: response.responseText,
                        })
                    }
                })
            }
        });

        // 连接数据库执行多条语句
        $('.sql-many').on('click', function () {
            let sql = editor.getSession().getValue();
            let names = $('.names').text();
            let status = $('.names').attr('data-status')
            if (status === '0') {
                $.alert({
                    title: 'Tips',
                    type: 'red',
                    content: '请正确连接数据库',
                })
            } else if (sql.length === 0) {
                $.alert({
                    title: 'Tips',
                    type: 'red',
                    content: '请正确输入SQL语句',
                })
            } else {
                $.ajax({
                    url: "{% url 'db_exec' %}",
                    type: 'POST',
                    data: {pk: $('#db_server').val(), sql: sql, sql_type: 'sql-many'},
                    success: function (res) {
                        if (res.code === 200) {
                            $('#sql-res-table').text('\n' + res.data);
                        } else {
                            $.alert({
                                title: 'Tips',
                                type: 'red',
                                content: res.msg,
                            })
                        }
                    },
                    error: function (response) {
                        $.alert({
                            title: 'Tips',
                            type: 'red',
                            content: response.responseText,
                        })
                    }
                })
            }
        });

        // 将查询结果导出到csv文件
        function sqlExport(pk) {
            location.href = '/db_config/db_log_detail/?pk=' + pk
        };

        //获取屏幕的高度
        let windowheight = $(window).height() - 40;
        let navbar = $('.navbar').height();
        let seachdb = $('.seach-db').height();
        let mainfooter = $('.main-footer').height();
        $('.table-names').css('height', windowheight - navbar - seachdb - mainfooter + 'px')


    </script>

{% endblock %}

